package com.zdkj.subway.stm.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zdkj.common.security.util.SecurityUtils;
import com.zdkj.subway.stm.dvo.SwStCheckDetailsDvo;
import com.zdkj.subway.stm.dvo.SwStReportqueryStationincomeExcelDvo;
import com.zdkj.subway.stm.entity.SwStReportqueryStationincome;
import com.zdkj.subway.stm.mapper.SwStReportqueryStationincomeMapper;
import com.zdkj.subway.stm.reportquery.util.StationSettleUtil;
import com.zdkj.subway.stm.service.SwStReportqueryStationincomeService;
import com.zdkj.subway.stm.util.JdbcUtil;
import com.zdkj.subway.stm.util.LocalDateConverter;
import com.zdkj.subway.stm.util.LocalDateTimeConverter;
import com.zdkj.subway.stm.util.TvmCheckDetails;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 车站营收日报表查询'
 * @author
 * @date 2020-12-29

 */
@Service
@Slf4j
public class SwStReportqueryStationincomeServiceImpl extends ServiceImpl<SwStReportqueryStationincomeMapper, SwStReportqueryStationincome> implements SwStReportqueryStationincomeService {


	/**
	 * 查询给定时间范围内的每一天的日期(yyyy-MM-dd HH:mm:ss")
	 *
	 * @param startDateStr 开始时间 (yyyy-MM-dd HH:mm:ss")
	 * @param endDateStr 结束时间 (yyyy-MM-dd HH:mm:ss")
	 * @return List<String>
	 */
	@Override
	public List<String> handleRangeDate(String startDateStr, String endDateStr) {
		List<String> listDate = new ArrayList<>();
		DateTimeFormatter df1 = DateTimeFormatter.ofPattern("yyyy-MM-dd");
		try {
			LocalDate startDate = LocalDate.parse(startDateStr, df1);
			LocalDate endDate = LocalDate.parse(endDateStr, df1);
			LocalDate tempDate = null;
			while (!startDate.plusDays(-1).equals(endDate)) {
				tempDate = startDate;
				String format = tempDate.format(df1);
				listDate.add(format);
				startDate = startDate.plusDays(1);
			}
			System.out.println(listDate.toString());
			return listDate;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<SwStReportqueryStationincome> calcStationIncomeDailyReport(String dateTime){
		return StationSettleUtil.calcStationIncomeDailyReport(dateTime);
	}

	@Override
	public List<SwStReportqueryStationincome> incomeDailyReport(String lineCodes,String dateTime){
		return StationSettleUtil.incomeDailyReport(lineCodes,dateTime);
	}

	/**
	 * 车站TVM钱箱清点记录表
	 * @param swStCheckDetailsDvo  收益日期 yyyy-MM-dd
	 * @return R
	 */
    @Override
    public List<SwStCheckDetailsDvo> getswStCheckDetails(SwStCheckDetailsDvo swStCheckDetailsDvo) {

		return this.baseMapper.getswStCheckDetails(swStCheckDetailsDvo);
    }

	/**
	 * 获取结算周期
	 * @param swStCheckDetailsDvo
	 * @return R
	 */
	@Override
	public List<Map<String, Object>> getswStCheckCkData(SwStCheckDetailsDvo swStCheckDetailsDvo) {
        String lineCode = swStCheckDetailsDvo.getLineCode();
        String stationCode = swStCheckDetailsDvo.getStationCode();
		String earningDate = swStCheckDetailsDvo.getEarningDate();
		List<Map<String, Object>> maps = new ArrayList<>();
		try {
            maps = JdbcUtil.exeSqlQuery("SELECT CASE WHEN a.num = 1 THEN a.begin_time ELSE b.startSettleDate END AS begin_time, " +
                    " CASE WHEN a.num = 1 THEN a.end_time ELSE b.endSettleDate END AS end_time FROM "+
                    " (SELECT a.line_code,a.station_code,DATE_FORMAT( a.END_TIME, '%Y%m%d' ) startSettleDate, "+
                    " (CASE WHEN (DAY ( date_add( a.END_TIME, INTERVAL 1 SECOND ) ) - DAY ( date_add( a.END_TIME, INTERVAL  "+
                    " ( ifnull( b.settlement_term, 1 ) ) DAY ) ) ) < 0 THEN DATE_FORMAT( date_add( a.END_TIME, INTERVAL "+
                    " ( ifnull( b.settlement_term, 1 ) ) DAY ), '%Y%m%d' ) ELSE DATE_FORMAT( last_day( a.END_TIME ), '%Y%m%d' ) "+
                    " END ) endSettleDate FROM SW_CK_DATA_SUBMIT a LEFT JOIN sw_pm_long_period_settlement b ON "+
                    " a.LINE_CODE = b.LINE_CODE AND a.STATION_CODE = b.STATION_CODE AND b.DEL_FLAG = '0' and b.STATUS = '1' WHERE " +
                    " a.LINE_CODE = '"+lineCode+"' AND a.STATION_CODE = '"+stationCode+"' ) b LEFT JOIN (" +
                    " SELECT count( 1 ) AS num,DATE_FORMAT( a.begin_time, '%Y%m%d' ) begin_time,DATE_FORMAT( DATE_ADD( " +
                    " DATE_FORMAT( a.end_time, '%Y%m%d' ), INTERVAL - 1 DAY ), '%Y%m%d' ) end_time,line_code,station_code " +
					" FROM sw_ck_data_submit_log a WHERE a.LINE_CODE = '"+lineCode+"' AND a.STATION_CODE = '"+stationCode+"' AND DATE_ADD( '"+earningDate+"',  " +
					" INTERVAL 2 HOUR ) BETWEEN a.begin_time AND a.end_time) a ON a.line_code = b.line_code " +
                    " AND a.station_code = b.station_code");
			System.out.println(maps);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return maps;
	}

	/**
	 * 按模版内容导出车站TVM钱箱清点记录表
	 * @param response
	 *
	 * @param swStCheckDetailsDvo      导出数据
	 * @param response
	 */
	@Override
	public void exportCheckDetails(SwStCheckDetailsDvo swStCheckDetailsDvo, HttpServletResponse response) {
        List<Map<String, Object>> swStCheckCkdate =  getswStCheckCkData(swStCheckDetailsDvo);
        for (Map<String, Object> item :swStCheckCkdate){
            swStCheckDetailsDvo.setBeginTime(String.valueOf(item.get("begin_time")));
            swStCheckDetailsDvo.setEndTime(String.valueOf(item.get("end_time")));
        }
		List<SwStCheckDetailsDvo> swStCheckDetailsList= this.baseMapper.getswStCheckDetails(swStCheckDetailsDvo);
		List<SwStCheckDetailsDvo> list = TvmCheckDetails.countCheckDetails(swStCheckDetailsList);
		String total = "";
		String difftotal = "";
		if(CollectionUtil.isNotEmpty(list)){
			difftotal = String.valueOf(list.get(list.size()-1).getCoinShowAmount());
			total = String.valueOf(list.get(list.size()-2).getCoinShowAmount());
			list = list.subList(0,list.size()-2);
		}

		InputStream excelTemplateIs = null;
		try {

			response.reset(); // 非常重要
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			final String fileName = URLEncoder.encode("车站TVM钱箱清点记录表", "UTF-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + System.currentTimeMillis() + ".xlsx");

			// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
			// {} 代表普通变量 {.} 代表是list的变量

			excelTemplateIs = this.getClass().getResourceAsStream("/static/excel/template/SwStTvmCheckDetailsExcelTemplate.xlsx");

			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerConverter(new LocalDateTimeConverter()).registerConverter(new LocalDateConverter()).withTemplate(excelTemplateIs).build();
			WriteSheet writeSheet = EasyExcel.writerSheet("车站TVM钱箱清点记录表").build();
			// 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
			// forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
			// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
			// 如果数据量大 list不是最后一行 参照另一个
			FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
			excelWriter.fill(list, fillConfig, writeSheet);

			Map<String, Object> map = new HashMap<>();
			String lineCode = SecurityUtils.getUser().getLineCode();
			String stationCode = SecurityUtils.getUser().getStationCode();
			String lineName = "";
			String stationName= "";
			List lst = getLineNameStationName(lineCode,stationCode);
			if(lst!=null && lst.size()>0){
				lineName  = String.valueOf(lst.get(0));
				stationName  = String.valueOf(lst.get(1));

			}
			map.put("earningDate", swStCheckDetailsDvo.getEarningDate());
			map.put("lineName", lineName);
			map.put("stationName", stationName);
			map.put("total", total);//清点金额
			map.put("difftotal", difftotal);//差异金额
			excelWriter.fill(map, writeSheet);

			excelWriter.finish();

		} catch (Exception e) {
			e.printStackTrace();
			log.error("导出车站TVM钱箱清点记录表错误");
		} finally {
			if(excelTemplateIs != null){
				try {
					excelTemplateIs.close();
				} catch (IOException ioe) {
					ioe.printStackTrace();
				}

			}
		}
	}

	/**
     * 按模版内容导出车站营收日报表
     * @param response
     *
     * @param swStReportqueryStationincomeExcelDvo      导出数据
     * @param response
     */

    @Override
    public void exportByPageContent(SwStReportqueryStationincomeExcelDvo swStReportqueryStationincomeExcelDvo,HttpServletResponse response) {
        InputStream excelTemplateIs = null;
        if (swStReportqueryStationincomeExcelDvo!=null) {

            try {
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                final String fileName = URLEncoder.encode("车站营收日报表", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + System.currentTimeMillis() + ".xlsx");

                excelTemplateIs = this.getClass().getResourceAsStream("/static/excel/template/SwStReportqueryStationincomeExcelDvo.xlsx");

                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerConverter(new LocalDateTimeConverter()).registerConverter(new LocalDateConverter()).withTemplate(excelTemplateIs).build();
                WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();

				//不需要列表的一行一行填充，不需要执行此方法
                //FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                //excelWriter.fill(swStReportqueryStationincomeExcelDvo, fillConfig, writeSheet);

                String lineCode = SecurityUtils.getUser().getLineCode();
				String stationCode = SecurityUtils.getUser().getStationCode();
				String lineName = "";
				String stationName= "";
				 List lst = getLineNameStationName(lineCode,stationCode);
				if(lst!=null && lst.size()>0){
                    lineName  = String.valueOf(lst.get(0));
					stationName  = String.valueOf(lst.get(1));

				}
				Map<String, Object> map = new HashMap<>();
                map.put("statisticsDate", swStReportqueryStationincomeExcelDvo.getStatisticsDate());
				map.put("currLineName",lineName);
				map.put("currStationName",stationName);
				map.put("cashBox",swStReportqueryStationincomeExcelDvo.getCashBox());
				map.put("supplment",swStReportqueryStationincomeExcelDvo.getSupplment());
				map.put("hand",swStReportqueryStationincomeExcelDvo.getHand());
				map.put("subTvmTotal",swStReportqueryStationincomeExcelDvo.getSubTvmTotal());
				map.put("marketTicket",swStReportqueryStationincomeExcelDvo.getMarketTicket());
				map.put("yzdcp",swStReportqueryStationincomeExcelDvo.getYzdcp());
				map.put("yjzp",swStReportqueryStationincomeExcelDvo.getYjzp());
				map.put("conductorRealAmount",swStReportqueryStationincomeExcelDvo.getConductorRealAmount());
				map.put("subBomTotal",swStReportqueryStationincomeExcelDvo.getSubBomTotal());
				map.put("conductorShort",swStReportqueryStationincomeExcelDvo.getConductorShort());
				map.put("addMoney",swStReportqueryStationincomeExcelDvo.getAddMoney());
				map.put("conductorLong",swStReportqueryStationincomeExcelDvo.getConductorLong());
				map.put("other",swStReportqueryStationincomeExcelDvo.getOther());
				map.put("total",swStReportqueryStationincomeExcelDvo.getTotal());
				map.put("passengerAttendName",swStReportqueryStationincomeExcelDvo.getPassengerAttendName());
				map.put("passengerAttendJobNo",swStReportqueryStationincomeExcelDvo.getPassengerAttendJobNo());
				map.put("attendName",swStReportqueryStationincomeExcelDvo.getAttendName());
				map.put("attendJobNo",swStReportqueryStationincomeExcelDvo.getAttendJobNo());
                excelWriter.fill(map, writeSheet);
                excelWriter.finish();

            }  catch (Exception e) {
                e.printStackTrace();
                //throw new Exception("车站营收日报表数据错误");
            } finally {
                if(excelTemplateIs != null){
                    try {
                        excelTemplateIs.close();
                    } catch (IOException ioe) {
                        ioe.printStackTrace();
                    }

                }
            }
        }

    }

	/**
	 * 车站营收日报表按日期范围导出
	 * @param response
	 *
	 * @param swStReportqueryStationincomeExcelDvo  导出数据
	 * @param response
	 */
	@Override
	public void exportByPageContentForCycle(SwStReportqueryStationincomeExcelDvo swStReportqueryStationincomeExcelDvo,HttpServletResponse response) {
		InputStream excelTemplateIs = null;
		if (swStReportqueryStationincomeExcelDvo!=null) {
			try {
				response.setContentType("application/vnd.ms-excel");
				response.setCharacterEncoding("utf-8");
				final String fileName = URLEncoder.encode("车站营收日报表", "UTF-8");
				response.setHeader("Content-disposition", "attachment;filename=" + fileName + System.currentTimeMillis() + ".xlsx");
				excelTemplateIs = this.getClass().getResourceAsStream("/static/excel/template/SwStReportqueryStationincomeForCycleExcelDvo.xlsx");
				ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerConverter(new LocalDateTimeConverter()).registerConverter(new LocalDateConverter()).withTemplate(excelTemplateIs).build();
				WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
				String lineCode = SecurityUtils.getUser().getLineCode();
				String stationCode = SecurityUtils.getUser().getStationCode();
				String lineName = "";
				String stationName= "";
				List lst = getLineNameStationName(lineCode,stationCode);
				if(lst!=null && lst.size()>0){
					lineName  = String.valueOf(lst.get(0));
					stationName  = String.valueOf(lst.get(1));
				}
				Map<String, Object> map = new HashMap<>();
				map.put("startTime", swStReportqueryStationincomeExcelDvo.getStartTime());
				map.put("endTime", swStReportqueryStationincomeExcelDvo.getEndTime());
				map.put("statisticsDate", swStReportqueryStationincomeExcelDvo.getStatisticsDate());
				map.put("currLineName",lineName);
				map.put("currStationName",stationName);
				map.put("cashBox",swStReportqueryStationincomeExcelDvo.getCashBox());
				map.put("supplment",swStReportqueryStationincomeExcelDvo.getSupplment());
				map.put("hand",swStReportqueryStationincomeExcelDvo.getHand());
				map.put("subTvmTotal",swStReportqueryStationincomeExcelDvo.getSubTvmTotal());
				map.put("marketTicket",swStReportqueryStationincomeExcelDvo.getMarketTicket());
				map.put("yzdcp",swStReportqueryStationincomeExcelDvo.getYzdcp());
				map.put("yjzp",swStReportqueryStationincomeExcelDvo.getYjzp());
				map.put("conductorRealAmount",swStReportqueryStationincomeExcelDvo.getConductorRealAmount());
				map.put("subBomTotal",swStReportqueryStationincomeExcelDvo.getSubBomTotal());
				map.put("conductorShort",swStReportqueryStationincomeExcelDvo.getConductorShort());
				map.put("addMoney",swStReportqueryStationincomeExcelDvo.getAddMoney());
				map.put("conductorLong",swStReportqueryStationincomeExcelDvo.getConductorLong());
				map.put("other",swStReportqueryStationincomeExcelDvo.getOther());
				map.put("total",swStReportqueryStationincomeExcelDvo.getTotal());
				map.put("passengerAttendName",swStReportqueryStationincomeExcelDvo.getPassengerAttendName());
				map.put("passengerAttendJobNo",swStReportqueryStationincomeExcelDvo.getPassengerAttendJobNo());
				map.put("attendName",swStReportqueryStationincomeExcelDvo.getAttendName());
				map.put("attendJobNo",swStReportqueryStationincomeExcelDvo.getAttendJobNo());
				excelWriter.fill(map, writeSheet);
				excelWriter.finish();
			}  catch (Exception e) {
				e.printStackTrace();
			} finally {
				if(excelTemplateIs != null){
					try {
						excelTemplateIs.close();
					} catch (IOException ioe) {
						ioe.printStackTrace();
					}
				}
			}
		}
	}

	private List getLineNameStationName(String lineCode,String stationCode){
	  List lst = new ArrayList();
	  try{
	  
	     String sql = "select a.name as linename,b.name as stationname from sw_param_line a,sw_param_station b where a.code=b.line_code  and a.del_flag='0' and b.del_flag='0' and a.code='"+lineCode+"' and b.code='"+stationCode+"' ";
	     List<Map<String,Object>> lstMap =  JdbcUtil.exeSqlQueryByCon(sql);
		 if(lstMap!=null && lstMap.size()>0){
		     lst.add((String)lstMap.get(0).get("linename"));
			 lst.add((String)lstMap.get(0).get("stationname"));
		 }
	  }catch(Exception e){
	      
	  }
	  return lst;
	}
	
	

}
